package com.sunda.spmsorder.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sunda.spmsorder.entity.MaterialCategory;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 物料分类表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2020-11-16
 */
@Mapper
@Component
public interface MaterialCategoryMapper extends BaseMapper<MaterialCategory> {

    @Select("SELECT mc.CATEGORY_ID AS \"categoryId\",\n" +
            "  mc.CATEGORY_LEVEL AS \"categoryLevel\",\n" +
            "  mc.PARENT_ID AS \"parentId\",\n" +
            "  mc.CATEGORY_NAME AS \"categoryName\",\n" +
            "  mc.CATEGORY_EN_NAME AS \"categoryENName\",\n" +
            "  mc.CATEGORY_RANK AS \"categoryRank\",\n" +
            "  mc.UPDATE_TIME AS\"updateTime\",\n" +
            "  mc.DESCRIPTION AS \"description\"\n" +
            "FROM MATERIAL_CATEGORY mc WHERE mc.IS_DELETED = '0' ORDER BY mc.CATEGORY_LEVEL, mc.PARENT_ID, mc.CATEGORY_RANK DESC")
    List<Map<String, Object>> getMaterialCategoryList();

    @Select("SELECT mc.CATEGORY_ID AS \"categoryId\",\n" +
            "       mc.CATEGORY_LEVEL AS \"categoryLevel\",\n" +
            "       mc.PARENT_ID AS \"parentId\",\n" +
            "       mc.CATEGORY_NAME AS \"categoryName\",\n" +
            "       mc.CATEGORY_EN_NAME AS \"categoryENName\",\n" +
            "       mc.CATEGORY_RANK AS \"categoryRank\",\n" +
            "       mc.DESCRIPTION AS \"description\",\n" +
            "       mc.CREATE_USERID AS \"createUserId\",\n" +
            "       u.LAST_NAME AS \"createUserName\",\n" +
            "       mc.CREATE_TIME AS \"createTime\",\n" +
            "       mc.UPDATE_USERID AS \"updateUserId\",\n" +
            "       u1.LAST_NAME AS \"updateUserName\",\n" +
            "       mc.UPDATE_TIME AS \"updateTime\",\n" +
            "       mc.IS_DELETED AS \"isDeleted\"\n" +
            "FROM MATERIAL_CATEGORY mc\n" +
            "  LEFT JOIN SPMS_USER u ON mc.CREATE_USERID = u.WORK_CODE\n" +
            "  LEFT JOIN SPMS_USER u1 ON mc.UPDATE_USERID = u1.WORK_CODE\n" +
            "WHERE mc.IS_DELETED = '0' ORDER BY mc.CATEGORY_LEVEL, mc.PARENT_ID, mc.CATEGORY_RANK DESC ")
    List<Map<String, Object>> getMaterialCategoryDtlList();

    @Select("SELECT\n" +
            "  sap.MATERIAL_NO AS \"materialNo\",\n" +
            "  sap.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "  sap.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "  sap.PRODUCT_LEVEL AS \"productLevel\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE AS \"productLevelLarge\",\n" +
            "  sap.PRODUCT_LEVEL_LARGE_DESC AS \"productLevelLargeDesc\",\n" +
            "  sap.PRODUCT_LEVEL_MID AS \"productLevelMid\",\n" +
            "  sap.PRODUCT_LEVEL_MID_DESC AS \"productLevelMidDesc\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL AS \"productLevelSmall\",\n" +
            "  sap.PRODUCT_LEVEL_SMALL_DESC AS \"productLevelSmallDesc\",\n" +
            "  sap.BRAND AS \"brand\",\n" +
            "  sap.BASIC_UNIT AS \"basicUnit\",\n" +
            "  sap.PURCHASE_UNIT AS \"purchaseUnit\",\n" +
            "  sap.NET_WEIGHT AS \"netWeight\",\n" +
            "  sap.GROSS_WEIGHT AS \"grossWeight\",\n" +
            "  sap.WEIGHT_UNIT AS \"weightUnit\",\n" +
            "  sap.VOLUME_QTY AS \"volumeQty\",\n" +
            "  sap.VOLUME_UNIT AS \"volumeUnit\",\n" +
            "  sap.LIGHT_HEAVY_MARK AS \"lightHeavyMark\",\n" +
            "  sap.IS_DELETE AS \"isDelete\",\n" +
            "  sap.MATERIAL_STATUS AS \"materialStatus\",\n" +
            "  sap.SPMS_MARK AS \"spmsMark\",\n" +
            "  sap.SPECS AS \"specs\",\n" +
            "  sap.MATERIAL_MODEL AS \"materialModel\",\n" +
            "  sap.SUPPLY_DESC AS \"supplyDesc\",\n" +
            "  sap.SUPPLIER_MATERIAL_NO AS \"supplierMaterialNo\",\n" +
            "  sap.CREATE_DATE AS \"createDate\",\n" +
            "  sap.MATERIAL_STANDARD AS \"materialStandard\"," +
            "  mp.PHOTOS AS \"photos\"\n" +
            "FROM MATERIAL_CATEGORY c\n" +
            "  LEFT JOIN MATERIAL_SPMS s ON c.CATEGORY_ID = s.CATEGORY_ID\n" +
            "  LEFT JOIN MATERIAL_SAP sap ON s.MATERIAL_NO = sap.MATERIAL_NO\n" +
            "  LEFT JOIN MATERIAL_PHOTOS mp ON sap.MATERIAL_NO = mp.MATERIAL_NO\n" +
            " WHERE c.CATEGORY_ID = #{categoryId} AND sap.MATERIAL_NO IS NOT NULL ")
    List<Map<String, Object>> getCategoryMaterial(@Param("categoryId") String categoryId);

    @Select("SELECT NVL(MAX(CATEGORY_ID), 0) FROM MATERIAL_CATEGORY MC WHERE CATEGORY_LEVEL = #{categoryLevel} AND PARENT_ID = #{parentId} ")
    Integer getMaxCategoryId(@Param("categoryLevel") Integer categoryLevel, @Param("parentId") Integer parentId);

    @Select("SELECT mc.CATEGORY_ID AS \"categoryId\",\n" +
            "       mc.CATEGORY_LEVEL AS \"categoryLevel\",\n" +
            "       mc.PARENT_ID AS \"parentId\",\n" +
            "       mc.CATEGORY_NAME AS \"categoryName\",\n" +
            "       mc.CATEGORY_EN_NAME AS \"categoryENName\",\n" +
            "       mc.CATEGORY_RANK AS \"categoryRank\",\n" +
            "       mc.DESCRIPTION AS \"description\",\n" +
            "       mc.CREATE_USERID AS \"createUserId\",\n" +
            "       u.LAST_NAME AS \"createUserName\",\n" +
            "       mc.CREATE_TIME AS \"createTime\",\n" +
            "       mc.UPDATE_USERID AS \"updateUserId\",\n" +
            "       u1.LAST_NAME AS \"updateUserName\",\n" +
            "       mc.UPDATE_TIME AS \"updateTime\",\n" +
            "       mc.IS_DELETED AS \"isDeleted\"\n" +
            "FROM MATERIAL_CATEGORY mc\n" +
            "  LEFT JOIN SPMS_USER u ON mc.CREATE_USERID = u.WORK_CODE\n" +
            "  LEFT JOIN SPMS_USER u1 ON mc.UPDATE_USERID = u1.WORK_CODE\n" +
            "WHERE mc.IS_DELETED = '1' ORDER BY mc.CATEGORY_LEVEL, mc.PARENT_ID, mc.CATEGORY_RANK DESC ")
    List<Map<String, Object>> getDeletedCategory();
}



